# coding=utf-8
# Author: Su JUnwei
import tkinter as tk
from tkinter import ttk
import sqlite3
import matplotlib.pyplot as plt
from tkinter import messagebox
import matplotlib
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
import hashlib
import sqlite3
from tkcalendar import DateEntry  #日历组件
import functools
from datetime import datetime

# 设置 Matplotlib 的默认字体
plt.rcParams['font.sans-serif'] = ['SimHei'] 
plt.rcParams['axes.unicode_minus'] = False  # 解决负号'-'显示为方块的问题
matplotlib.use('TkAgg')  # 使用 TkAgg 作为后端

# 初始化数据库
def create_db():
    conn = sqlite3.connect('accounting.db')
    c = conn.cursor()

    # 创建交易记录表，添加 user_id 字段
    c.execute('''
        CREATE TABLE IF NOT EXISTS transactions
        (id INTEGER PRIMARY KEY, user_id INTEGER, sequence_number INTEGER, date TEXT, amount REAL, type TEXT, category TEXT, payment_method TEXT, FOREIGN KEY(user_id) REFERENCES users(id))
    ''')

    # 创建用户表
    c.execute('''
        CREATE TABLE IF NOT EXISTS users
        (id INTEGER PRIMARY KEY, username TEXT UNIQUE, password TEXT)
    ''')

    # 添加 sequence_number 列
    # c.execute('ALTER TABLE transactions ADD COLUMN sequence_number INTEGER')

    conn.commit()
    conn.close()

create_db()

current_user_id = None

def register_user(username, password):
    global current_user_id
    conn = sqlite3.connect('accounting.db')
    c = conn.cursor()
    hashed_password = hashlib.sha256(password.encode()).hexdigest()
    try:
        c.execute("INSERT INTO users (username, password) VALUES (?, ?)", (username, hashed_password))
        conn.commit()

        # 检索新用户的 user_id
        c.execute("SELECT id FROM users WHERE username = ?", (username,))
        user_id = c.fetchone()[0]
        current_user_id = user_id  # 设置 current_user_id

    except sqlite3.IntegrityError:
        messagebox.showerror("错误", "用户名已存在")
        return
    finally:
        conn.close()
    return True  # 返回成功标志
    
def login_user(username, password):
    global current_user_id
    conn = sqlite3.connect('accounting.db')
    c = conn.cursor()
    c.execute("SELECT id, password FROM users WHERE username = ?", (username,))
    result = c.fetchone()
    conn.close()
    if result and hashlib.sha256(password.encode()).hexdigest() == result[1]:
        current_user_id = result[0]
        # update_transactions()  # 刷新交易记录
        return True
    else:
        return False

def update_existing_sequence_numbers():
    conn = sqlite3.connect('accounting.db')
    c = conn.cursor()

    # 获取所有用户的 ID
    c.execute("SELECT id FROM users")
    user_ids = c.fetchall()

    for user_id in user_ids:
        # 对每个用户的交易按日期排序并更新序号
        c.execute("SELECT id FROM transactions WHERE user_id = ? ORDER BY date", (user_id[0],))
        transaction_ids = c.fetchall()

        sequence_number = 1
        for transaction_id in transaction_ids:
            c.execute("UPDATE transactions SET sequence_number = ? WHERE id = ?", (sequence_number, transaction_id[0]))
            sequence_number += 1

    conn.commit()
    conn.close()

update_existing_sequence_numbers()

# 设置GUI
def setup_gui():
    # 创建主窗口
    root = tk.Tk()
    root.title("智绘钱迹")

    # 注册界面
    register_frame = ttk.Frame(root)
    # register_frame.pack(padx=10, pady=10, fill="x", expand=True)  # 初始时不显示注册界面

    # 注册界面的用户名输入
    register_username_entry = ttk.Entry(register_frame)
    ttk.Label(register_frame, text="用户名:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
    register_username_entry.grid(row=0, column=1, sticky="ew", padx=5, pady=5)

    # 注册界面的密码输入
    register_password_entry = ttk.Entry(register_frame, show="*")
    ttk.Label(register_frame, text="密码:").grid(row=1, column=0, sticky="w", padx=5, pady=5)
    register_password_entry.grid(row=1, column=1, sticky="ew", padx=5, pady=5)

    # 用户登录界面
    login_frame = ttk.Frame(root)
    login_frame.pack(padx=10, pady=10, fill="x", expand=True)

    username_entry = ttk.Entry(login_frame)
    ttk.Label(login_frame, text="用户名:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
    username_entry.grid(row=0, column=1, sticky="ew", padx=5, pady=5)

    password_entry = ttk.Entry(login_frame, show="*")
    ttk.Label(login_frame, text="密码:").grid(row=1, column=0, sticky="w", padx=5, pady=5)
    password_entry.grid(row=1, column=1, sticky="ew", padx=5, pady=5)

    def create_edit_components(frame):
        global edit_date_entry, edit_amount_entry, edit_type_combobox, edit_category_entry, edit_payment_method_entry
        edit_date_entry = DateEntry(frame)
        edit_amount_entry = ttk.Entry(frame)
        edit_type_combobox = ttk.Combobox(frame, values=["收入", "支出"])
        edit_category_entry = ttk.Entry(frame)
        edit_payment_method_entry = ttk.Entry(frame)

    # 创建记账界面相关组件，但初始时不展示
    input_frame = ttk.Frame(root)
    transactions_frame = ttk.Frame(root)
    edit_frame = ttk.Frame(root)
    create_edit_components(edit_frame)
    edit_frame.pack_forget()

    def on_register():
        # 获取注册界面的用户名和密码
        username = register_username_entry.get()
        password = register_password_entry.get()

        # 验证输入是否有效
        if not username or not password:
            messagebox.showerror("错误", "用户名和密码不能为空")
            return

        # 调用 register_user 函数
        if register_user(username, password):
            messagebox.showinfo("成功", "注册成功")
            # 隐藏注册界面
            register_frame.pack_forget()

            # 显示记账界面的相关组件
            input_frame.pack(padx=10, pady=10, fill="x", expand=True)
            transactions_frame.pack(fill="both", expand=True, padx=10, pady=10)

            # 可选：更新交易记录或执行其他初始化操作
            update_transactions()

        else:
            return  # 注册失败时直接返回

    register_button = ttk.Button(register_frame, text="注册", command=on_register)
    register_button.grid(row=3, column=0, columnspan=2, pady=10)

    def on_login():
        # 获取输入的用户名和密码
        username = username_entry.get()
        password = password_entry.get()

        # 验证输入是否有效
        if not username or not password:
            messagebox.showerror("错误", "用户名和密码不能为空")
            return

        # 调用 login_user 函数
        if login_user(username, password):
            messagebox.showinfo("成功", "登录成功")

            # 隐藏登录界面
            login_frame.pack_forget()

            # 显示记账界面的相关组件
            input_frame.pack(padx=10, pady=10, fill="x", expand=True)
            transactions_frame.pack(fill="both", expand=True, padx=10, pady=10)

            # 可选：更新交易记录或执行其他初始化操作
            update_transactions()
        else:
            messagebox.showerror("失败", "登录失败，请检查用户名和密码")

    login_button = ttk.Button(login_frame, text="登录", command=on_login)
    login_button.grid(row=3, column=0, columnspan=2, pady=10)

    # 切换到注册界面的按钮（可选）
    def show_register_frame():
        login_frame.pack_forget()
        register_frame.pack(padx=10, pady=10, fill="x", expand=True)

    show_register_button = ttk.Button(login_frame, text="注册新账号", command=show_register_frame)
    show_register_button.grid(row=4, column=0, columnspan=2, pady=10)

    # 切换到登录界面的按钮（可选）
    def show_login_frame():
        register_frame.pack_forget()
        login_frame.pack(padx=10, pady=10, fill="x", expand=True)

    show_login_button = ttk.Button(register_frame, text="已有账号？登录", command=show_login_frame)
    show_login_button.grid(row=4, column=0, columnspan=2, pady=10)

    # 显示登录界面
    login_frame.pack(padx=10, pady=10, fill="x", expand=True)

    # 日期输入 - 使用 DateEntry
    date_entry = DateEntry(input_frame, width=12, background='darkblue', foreground='white', borderwidth=2, date_pattern='yyyy/mm/dd')
    ttk.Label(input_frame, text="日期:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
    date_entry.grid(row=0, column=1, sticky="ew", padx=5, pady=5)

    # 金额输入
    amount_entry = ttk.Entry(input_frame)
    ttk.Label(input_frame, text="金额:").grid(row=1, column=0, sticky="w", padx=5, pady=5)
    amount_entry.grid(row=1, column=1, sticky="ew", padx=5, pady=5)

    # 类型选择
    type_combobox = ttk.Combobox(input_frame, values=["收入", "支出"])
    ttk.Label(input_frame, text="类型:").grid(row=2, column=0, sticky="w", padx=5, pady=5)
    type_combobox.grid(row=2, column=1, sticky="ew", padx=5, pady=5)

    # 分类输入
    category_entry = ttk.Entry(input_frame)
    ttk.Label(input_frame, text="分类:").grid(row=3, column=0, sticky="w", padx=5, pady=5)
    category_entry.grid(row=3, column=1, sticky="ew", padx=5, pady=5)

    # 支付方式输入
    payment_method_entry = ttk.Entry(input_frame)
    ttk.Label(input_frame, text="支付方式:").grid(row=4, column=0, sticky="w", padx=5, pady=5)
    payment_method_entry.grid(row=4, column=1, sticky="ew", padx=5, pady=5)

    # 编辑与注销账户设置
    def open_account_settings():
        settings_window = tk.Toplevel(root)
        settings_window.title("账户设置")

        # 用户名输入
        new_username_entry = ttk.Entry(settings_window)
        ttk.Label(settings_window, text="新用户名:").pack(padx=5, pady=5)
        new_username_entry.pack(padx=5, pady=5)

        # 密码输入
        new_password_entry = ttk.Entry(settings_window, show="*")
        ttk.Label(settings_window, text="新密码:").pack(padx=5, pady=5)
        new_password_entry.pack(padx=5, pady=5)

        # 提交按钮
        submit_button = ttk.Button(settings_window, text="提交更改", command=lambda: update_account_info(new_username_entry.get(), new_password_entry.get()))
        submit_button.pack(padx=5, pady=5)

        # 添加注销账户按钮
        delete_account_button = ttk.Button(settings_window, text="注销账户", command=lambda: delete_account(settings_window))
        delete_account_button.pack(padx=5, pady=5)

    def delete_account(settings_window):
        # 弹出确认对话框
        response = messagebox.askyesno("确认", "您确定要注销您的账户吗？这将删除所有相关数据且无法撤销。")
        if response:
            # 用户确认注销
            conn = sqlite3.connect('accounting.db')
            c = conn.cursor()
            try:
                # 删除用户数据
                c.execute("DELETE FROM transactions WHERE user_id = ?", (current_user_id,))
                c.execute("DELETE FROM users WHERE id = ?", (current_user_id,))
                conn.commit()
                messagebox.showinfo("成功", "账户已注销")
                
                # 关闭当前界面并显示注册界面
                settings_window.destroy()
                input_frame.pack_forget()
                transactions_frame.pack_forget()
                show_register_frame()

            except Exception as e:
                messagebox.showerror("错误", f"无法注销账户: {e}")
            finally:
                conn.close()

    def update_account_info(new_username, new_password):
        if not new_username or not new_password:
            messagebox.showerror("错误", "用户名和密码不能为空")
            return

        # 连接数据库并更新用户名和密码
        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()
        hashed_password = hashlib.sha256(new_password.encode()).hexdigest()  # 加密新密码
        try:
            c.execute("UPDATE users SET username = ?, password = ? WHERE id = ?", (new_username, hashed_password, current_user_id))
            conn.commit()
            messagebox.showinfo("成功", "账户信息已更新")
        except sqlite3.IntegrityError:
            messagebox.showerror("错误", "用户名已存在")
        finally:
            conn.close()

    # 在主界面的合适位置添加这个按钮
    account_settings_button = ttk.Button(input_frame, text="账户设置", command=open_account_settings)
    account_settings_button.grid(row=0, column=5, columnspan=2)  # 调整grid参数

    # 添加交易函数
    def add_transaction():
        # 获取输入数据
        date = date_entry.get()
        amount = amount_entry.get()
        transaction_type = type_combobox.get()
        category = category_entry.get()
        payment_method = payment_method_entry.get()

        # 插入数据到数据库，包含当前用户的 user_id 和序号
        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()

        # 获取当前用户的最大序号
        c.execute("SELECT MAX(sequence_number) FROM transactions WHERE user_id = ?", (current_user_id,))
        max_sequence = c.fetchone()[0]
        next_sequence = 1 if max_sequence is None else max_sequence + 1

        # 插入新交易，包含计算出的序号
        c.execute("INSERT INTO transactions (user_id, sequence_number, date, amount, type, category, payment_method) VALUES (?, ?, ?, ?, ?, ?, ?)",
                (current_user_id, next_sequence, date, amount, transaction_type, category, payment_method))
        conn.commit()
        conn.close()

        # 更新交易记录
        update_transactions()

    def delete_transaction():
        selected_item = tree.selection()  # 获取选中的项目
        if not selected_item:
            messagebox.showinfo("提示", "请先选择要删除的交易记录")
            return

        # 确认删除
        if not messagebox.askyesno("确认", "确定要删除这条交易记录吗？"):
            return

        # 获取要删除的交易记录的序号
        sequence_to_delete = tree.set(selected_item[0], "序号")

        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()
        
        # 删除选中的交易记录
        c.execute("DELETE FROM transactions WHERE user_id = ? AND sequence_number = ?", (current_user_id, sequence_to_delete))
        
        # 更新剩余交易记录的序号
        c.execute("SELECT id FROM transactions WHERE user_id = ? AND sequence_number > ? ORDER BY sequence_number", (current_user_id, sequence_to_delete))
        subsequent_transactions = c.fetchall()
        for trans_id, in subsequent_transactions:
            c.execute("UPDATE transactions SET sequence_number = sequence_number - 1 WHERE id = ?", (trans_id,))

        conn.commit()
        conn.close()

        # 刷新交易记录显示
        update_transactions()

    def edit_transaction():
        selected_item = tree.selection()
        if not selected_item:
            messagebox.showinfo("提示", "请先选择要编辑的交易记录")
            return

        sequence_to_edit = tree.set(selected_item[0], "序号")

        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()
        c.execute("SELECT sequence_number, date, amount, type, category, payment_method FROM transactions WHERE user_id = ? AND sequence_number = ?", (current_user_id, sequence_to_edit))
        record = c.fetchone()
        conn.close()

        # 清除 edit_frame 中现有的所有组件
        for widget in edit_frame.winfo_children():
            widget.destroy()

        # 显示编辑框架
        edit_frame.pack(padx=10, pady=10, fill="x", expand=True)

        # 确保组件存在，否则重新创建
        if not edit_date_entry.winfo_exists():
            create_edit_components(edit_frame)

        # 使用 grid 布局排列组件
        ttk.Label(edit_frame, text=f"序号: {record[0]}").grid(row=0, column=0)

        ttk.Label(edit_frame, text="编辑金额:").grid(row=2, column=0)
        edit_amount_entry.delete(0, tk.END)
        edit_amount_entry.insert(0, record[2])
        edit_amount_entry.grid(row=2, column=1)

        ttk.Label(edit_frame, text="编辑日期:").grid(row=1, column=0)
        edit_date_entry.set_date(datetime.strptime(record[1], "%Y/%m/%d").date())
        edit_date_entry.grid(row=1, column=1)

        ttk.Label(edit_frame, text="编辑类型:").grid(row=0, column=2)
        edit_type_combobox.set(record[3])
        edit_type_combobox.grid(row=0, column=3)

        ttk.Label(edit_frame, text="编辑分类:").grid(row=1, column=2)
        edit_category_entry.delete(0, tk.END)
        edit_category_entry.insert(0, record[4])
        edit_category_entry.grid(row=1, column=3)

        ttk.Label(edit_frame, text="编辑支付方式:").grid(row=2, column=2)
        edit_payment_method_entry.delete(0, tk.END)
        edit_payment_method_entry.insert(0, record[5])
        edit_payment_method_entry.grid(row=2, column=3)

        # 添加确认编辑按钮
        confirm_edit_button = ttk.Button(edit_frame, text="确认编辑", command=lambda: confirm_edit(sequence_to_edit))
        confirm_edit_button.grid(row=6, column=0, columnspan=2)

    def convert_date_format(date_str):
        # 尝试按照 yyyy/mm/dd 格式解析日期
        try:
            return datetime.strptime(date_str, '%Y/%m/%d').strftime('%Y/%m/%d')
        except ValueError:
            pass

        # 尝试按照 mm/dd/yy 格式解析日期
        try:
            return datetime.strptime(date_str, '%m/%d/%y').strftime('%Y/%m/%d')
        except ValueError:
            pass

        # 如果都不匹配，返回原始字符串或抛出错误
        return date_str

    # 确认编辑函数
    def confirm_edit(sequence_to_edit):
        new_date = convert_date_format(edit_date_entry.get())
        new_amount = edit_amount_entry.get()
        new_type = edit_type_combobox.get()
        new_category = edit_category_entry.get()
        new_payment_method = edit_payment_method_entry.get()

        # 更新数据库中的交易记录
        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()
        c.execute("UPDATE transactions SET date = ?, amount = ?, type = ?, category = ?, payment_method = ? WHERE user_id = ? AND sequence_number = ?", 
                (new_date, new_amount, new_type, new_category, new_payment_method, current_user_id, sequence_to_edit))
        conn.commit()
        conn.close()
        update_transactions()  # 刷新交易记录
        edit_frame.pack_forget()

    # 添加编辑按钮
    edit_button = ttk.Button(input_frame, text="编辑交易", command=lambda: edit_transaction())
    edit_button.grid(row=2, column=3, columnspan=2, pady=10)

    # 删除交易按钮
    delete_button = ttk.Button(input_frame, text="删除交易", command=delete_transaction)
    delete_button.grid(row=1, column=3, columnspan=2, pady=10)  # 根据需要调整网格参数

    # 添加交易按钮
    add_button = ttk.Button(input_frame, text="添加交易", command=add_transaction)
    add_button.grid(row=0, column=3, columnspan=2, pady=10)

    def sort_by(tree, col, descending):
        """ 对 Treeview 排序 """
        data = [(tree.set(child, col), child) for child in tree.get_children('')]

        # 根据列的数据类型选择排序方式
        if col in ['序号', '数额']:  # 假设这些列是数字类型
            data = [(int(num), child) for num, child in data]
        # 其他列默认为文本类型，按字符串排序

        # 排序数据
        data.sort(reverse=descending)

        for i, item in enumerate(data):
            tree.move(item[1], '', i)

        # 切换排序方向
        tree.heading(col, command=lambda: sort_by(tree, col, not descending))

    # 配置交易记录树视图
    tree = ttk.Treeview(transactions_frame, columns=("序号", "日期", "数额", "收支类型", "账单种类", "支付方式"), show="headings")
    for col in ("序号", "日期", "数额", "收支类型", "账单种类", "支付方式"):
        tree.heading(col, text=col, command=lambda _col=col: sort_by(tree, _col, False))
        tree.column(col, width=100, minwidth=50, stretch=tk.YES)  # 可以根据需要调整列宽度
    tree.pack(fill="both", expand=True)

    def update_transactions():
        # 清空旧数据
        for i in tree.get_children():
            tree.delete(i)

        # 从数据库加载当前用户的新数据
        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()
        # 确保选择语句包括 sequence_number 字段
        c.execute("SELECT sequence_number, date, amount, type, category, payment_method FROM transactions WHERE user_id = ? ORDER BY sequence_number", (current_user_id,))
        for row in c.fetchall():
            # 每行数据已包含序号
            tree.insert("", tk.END, values=row)
        conn.close()

    update_transactions()  # 初始化时更新交易记录

    def show_charts():
        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()

        # 修改 SQL 查询，只统计当前用户的支出
        c.execute("SELECT category, SUM(amount) FROM transactions WHERE user_id = ? AND type='支出' GROUP BY category", (current_user_id,))
        data = c.fetchall()
        conn.close()

        # 如果没有数据，则不执行绘图操作
        if not data:
            messagebox.showinfo("提示", "没有足够的数据来显示图表。")
            return

        categories = [row[0] for row in data if row[0]]  # 确保没有空的类别
        amounts = [row[1] for row in data if row[0]]

        # 检查是否有有效的数据点
        if not categories or not amounts:
            messagebox.showinfo("提示", "类别数据不足以生成图表。")
            return

        # 使用 subplots 创建一个 1x2 的图形布局
        fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6))

        # 第一个子图：直方图
        ax1.bar(categories, amounts, color='blue')
        ax1.set_xlabel('分类')
        ax1.set_ylabel('总支出')
        ax1.set_title('支出按类别统计')
        ax1.tick_params(axis='x', rotation=45)  # 旋转x轴标签

        # 第二个子图：饼图
        ax2.pie(amounts, labels=categories, autopct='%1.1f%%', startangle=140)
        ax2.set_title('支出分类比例')

        plt.tight_layout()
        plt.show()
        conn.close()


    # 显示图表按钮
    show_charts_button = ttk.Button(input_frame, text="显示图表", command=show_charts)
    show_charts_button.grid(row=3, column=3, columnspan=2)

    # 生成报表函数
    def generate_report():
        conn = sqlite3.connect('accounting.db')
        c = conn.cursor()

        # 修改 SQL 查询，只生成当前用户的月度报表
        c.execute("SELECT date, category, amount FROM transactions WHERE user_id = ? AND type='支出'", (current_user_id,))
        transactions = c.fetchall()

        report_output = "支出报表:\n"
        for trans in transactions:
            report_output += f"日期: {trans[0]}, 类别: {trans[1]}, 金额: {trans[2]}\n"

        # 显示在GUI中
        report_window = tk.Toplevel(root)
        report_text = tk.Text(report_window)
        report_text.insert(tk.END, report_output)
        report_text.pack()

        conn.close()

    # 生成报表按钮
    generate_report_button = ttk.Button(input_frame, text="生成报表", command=generate_report)
    generate_report_button.grid(row=4, column=3, columnspan=2)

    root.mainloop()

setup_gui()
